﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using seejee_Backendium.Core.Interfaces;
using seejee_Backendium.Core.Models;

namespace seejee_Backendium.Data.Services
{
    public class ProcedureService : IProcedureService
    {
        private readonly string _defaultConnectionString;
        private readonly ILogger<ProcedureService> _logger;
        private readonly IDynamicConnectionService _dynamicConnectionService;
        private readonly IConnectionStringFactory _connectionStringFactory;
        private readonly IConnectionPoolService _connectionPoolService;
        private readonly IMemoryCache _memoryCache;

        public ProcedureService(
            IConfiguration config,
            ILogger<ProcedureService> logger,
            IDynamicConnectionService dynamicConnectionService,
            IConnectionStringFactory connectionStringFactory,
            IConnectionPoolService connectionPoolService,
            IMemoryCache memoryCache)
        {
            _defaultConnectionString = config.GetConnectionString("Default");
            _logger = logger;
            _dynamicConnectionService = dynamicConnectionService;
            _connectionStringFactory = connectionStringFactory;
            _connectionPoolService = connectionPoolService;
            _memoryCache = memoryCache;
        }

        #region 原有方法（保持不变，兼容其他调用场景）
        public async Task<List<Dictionary<string, object>>> ExecuteProcedureAsync(ProcedureDto procedureDto)
        {
            // 从工厂获取动态连接字符串（原有逻辑）
            string dataSourcesConnectionString = _connectionStringFactory.GetDataSourcesConnectionString();
            if (string.IsNullOrEmpty(dataSourcesConnectionString))
            {
                throw new Exception("未设置数据源连接字符串");
            }

            try
            {
                if (!await ProcedureExistsInDataSourcesDBAsync(procedureDto.sp_name, dataSourcesConnectionString))
                {
                    _logger.LogInformation($"存储过程 {procedureDto.sp_name} 在目标数据库中不存在，将创建临时存储过程");
                    await CreateTemporaryProcedureInDataSourcesDB(procedureDto.sp_name, procedureDto.sp_text, dataSourcesConnectionString);
                }

                return await ExecuteDynamicProcedureInDataSourcesDB(procedureDto.sp_name, dataSourcesConnectionString);
            }
            finally
            {
                await DropTemporaryProcedureFromDataSourcesDB(procedureDto.sp_name, dataSourcesConnectionString);
                _logger.LogInformation($"已清理目标数据库中的临时存储过程 {procedureDto.sp_name}");
            }
        }

        public async Task<List<Dictionary<string, object>>> ExecuteExistingProcedureAsync(string procedureName, Dictionary<string, object> parameters = null, string connectionString = null)
        {
            var targetConnectionString = connectionString ?? _connectionStringFactory.GetDataSourcesConnectionString();
            return await ExecuteDynamicProcedureInDataSourcesDB(procedureName, targetConnectionString, parameters);
        }

        public async Task<List<Dictionary<string, object>>> ExecuteRawQueryInDataSourcesDBAsync(string sqlQuery, Dictionary<string, object> parameters = null, string connectionString = null)
        {
            var targetConnectionString = connectionString ?? _connectionStringFactory.GetDataSourcesConnectionString();
            return await ExecuteRawQueryAsync(sqlQuery, targetConnectionString, parameters);
        }
        #endregion

        #region 新增核心方法（使用连接池和缓存）
        /// <summary>
        /// 通过动态传入的连接字符串执行存储过程（使用连接池和缓存优化）
        /// </summary>
        public async Task<List<Dictionary<string, object>>> ExecuteProcedureWithConnectionAsync(
            ProcedureDto procedureDto, string connectionString)
        {
            // 参数验证
            if (procedureDto == null)
                throw new ArgumentNullException(nameof(procedureDto));

            if (string.IsNullOrWhiteSpace(procedureDto.sp_name))
                throw new ArgumentException("存储过程名称不能为空");

            if (string.IsNullOrWhiteSpace(procedureDto.sp_text))
                throw new ArgumentException("存储过程文本不能为空");

            if (string.IsNullOrWhiteSpace(connectionString))
                throw new ArgumentException("连接字符串不能为空");

            // 生成结果缓存键
            var resultCacheKey = $"procedure_result_{connectionString.GetHashCode()}_{procedureDto.sp_name}";

            // 检查结果缓存
            if (_memoryCache.TryGetValue(resultCacheKey, out List<Dictionary<string, object>> cachedResult))
            {
                _logger.LogInformation($"使用缓存结果执行存储过程: {procedureDto.sp_name}");
                return cachedResult;
            }

            SqlConnection connection = null;
            try
            {
                // 1. 从连接池获取连接
                connection = await _connectionPoolService.GetConnectionAsync(connectionString);

                // 2. 检查存储过程是否存在（添加缓存）
                var procedureExistsCacheKey = $"procedure_exists_{connectionString.GetHashCode()}_{procedureDto.sp_name}";
                bool procedureExists;

                if (!_memoryCache.TryGetValue(procedureExistsCacheKey, out procedureExists))
                {
                    procedureExists = await ProcedureExistsInDataSourcesDBAsync(procedureDto.sp_name, connection);
                    _memoryCache.Set(procedureExistsCacheKey, procedureExists, TimeSpan.FromMinutes(30));
                }

                // 3. 如果不存在，创建临时存储过程
                if (!procedureExists)
                {
                    _logger.LogInformation($"创建临时存储过程: {procedureDto.sp_name}");
                    await CreateTemporaryProcedureInDataSourcesDB(procedureDto.sp_name, procedureDto.sp_text, connection);
                }

                // 4. 执行存储过程
                var result = await ExecuteDynamicProcedureInDataSourcesDB(procedureDto.sp_name, connection);

                // 5. 缓存结果
                _memoryCache.Set(resultCacheKey, result, TimeSpan.FromMinutes(5));

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "执行存储过程失败。ProcedureName: {ProcedureName}, ConnectionString: {ConnectionString}",
                    procedureDto.sp_name, MaskConnectionString(connectionString));
                throw;
            }
            finally
            {
                // 6. 归还连接到池中
                if (connection != null)
                {
                    await _connectionPoolService.ReturnConnectionAsync(connectionString, connection);
                }
            }
        }
        #endregion

        #region 私有辅助方法

        /// <summary>
        /// 检查存储过程是否存在（使用连接字符串的版本 - 兼容原有代码）
        /// </summary>
        private async Task<bool> ProcedureExistsInDataSourcesDBAsync(string procedureName, string connectionString)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                return await ProcedureExistsInDataSourcesDBAsync(procedureName, connection);
            }
        }

        /// <summary>
        /// 检查存储过程是否存在（使用连接对象的版本 - 连接池优化）
        /// </summary>
        private async Task<bool> ProcedureExistsInDataSourcesDBAsync(string procedureName, SqlConnection connection)
        {
            var sql = @"
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.ROUTINES 
            WHERE ROUTINE_TYPE = 'PROCEDURE' 
              AND ROUTINE_NAME = @procedureName";

            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@procedureName", procedureName);
                int count = (int)await command.ExecuteScalarAsync();
                return count > 0;
            }
        }

        /// <summary>
        /// 创建临时存储过程（使用连接字符串的版本 - 兼容原有代码）
        /// </summary>
        private async Task CreateTemporaryProcedureInDataSourcesDB(string procedureName, string procedureText, string connectionString)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                await CreateTemporaryProcedureInDataSourcesDB(procedureName, procedureText, connection);
            }
        }

        /// <summary>
        /// 创建临时存储过程（使用连接对象的版本 - 连接池优化）
        /// </summary>
        private async Task CreateTemporaryProcedureInDataSourcesDB(string procedureName, string procedureText, SqlConnection connection)
        {
            // 参数验证
            if (string.IsNullOrWhiteSpace(procedureName))
            {
                throw new ArgumentException("存储过程名称不能为空", nameof(procedureName));
            }

            if (string.IsNullOrWhiteSpace(procedureText))
            {
                throw new ArgumentException("存储过程文本不能为空", nameof(procedureText));
            }

            try
            {
                _logger.LogInformation($"开始创建/修改存储过程: {procedureName}");

                // 关键修改：使用 CREATE OR ALTER 替代 CREATE
                string createOrAlterSql = procedureText;

                // 如果原始SQL是 CREATE PROCEDURE，替换为 CREATE OR ALTER PROCEDURE
                if (createOrAlterSql.Trim().ToUpper().StartsWith("CREATE PROCEDURE"))
                {
                    createOrAlterSql = "CREATE OR ALTER " + createOrAlterSql.Trim().Substring(6);
                }

                _logger.LogDebug($"最终执行的SQL: {createOrAlterSql}");

                using (var command = new SqlCommand(createOrAlterSql, connection))
                {
                    // 确保 CommandText 已正确设置
                    if (string.IsNullOrEmpty(command.CommandText))
                    {
                        throw new InvalidOperationException("CommandText 未正确初始化");
                    }

                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = 300; // 5分钟超时

                    await command.ExecuteNonQueryAsync();
                    _logger.LogInformation($"成功创建/修改存储过程: {procedureName}");
                }
            }
            catch (SqlException sqlEx) when (sqlEx.Number == 2714) // 对象已存在错误
            {
                // 如果仍然出现对象已存在错误，说明存储过程已经存在，这是正常的
                _logger.LogInformation($"存储过程 {procedureName} 已存在，继续执行");
                // 不抛出异常，继续执行
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "创建存储过程失败。ProcedureName: {ProcedureName}", procedureName);
                throw;
            }
        }

        /// <summary>
        /// 验证是否是有效的 CREATE PROCEDURE 语句
        /// </summary>
        private bool IsValidCreateProcedureStatement(string procedureText)
        {
            if (string.IsNullOrWhiteSpace(procedureText))
                return false;

            // 转换为大写进行验证
            var upperText = procedureText.Trim().ToUpper();

            // 检查是否包含 CREATE PROCEDURE 关键字
            if (!upperText.Contains("CREATE PROCEDURE") && !upperText.Contains("CREATE PROC"))
            {
                _logger.LogWarning("存储过程文本不包含 CREATE PROCEDURE 关键字");
                return false;
            }

            // 检查是否有明显的SQL注入风险
            if (upperText.Contains("DROP DATABASE") ||
                upperText.Contains("DELETE FROM") ||
                upperText.Contains("TRUNCATE TABLE"))
            {
                _logger.LogWarning("存储过程文本包含危险操作");
                return false;
            }

            return true;
        }


        /// <summary>
        /// 删除临时存储过程（使用连接字符串的版本 - 兼容原有代码）
        /// </summary>
        private async Task DropTemporaryProcedureFromDataSourcesDB(string procedureName, string connectionString)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    await DropTemporaryProcedureFromDataSourcesDB(procedureName, connection);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"清理临时存储过程 {procedureName} 时发生错误");
            }
        }

        /// <summary>
        /// 删除临时存储过程（使用连接对象的版本 - 连接池优化）
        /// </summary>
        private async Task DropTemporaryProcedureFromDataSourcesDB(string procedureName, SqlConnection connection)
        {
            try
            {
                string dropSql = $"DROP PROCEDURE IF EXISTS {EscapeSqlIdentifier(procedureName)}";
                using (var command = new SqlCommand(dropSql, connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"清理临时存储过程 {procedureName} 时发生错误");
            }
        }

        /// <summary>
        /// 执行存储过程（使用连接字符串的版本 - 兼容原有代码）
        /// </summary>
        private async Task<List<Dictionary<string, object>>> ExecuteDynamicProcedureInDataSourcesDB(
            string procedureName, string connectionString, Dictionary<string, object> parameters = null)
        {
            var results = new List<Dictionary<string, object>>();

            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                return await ExecuteDynamicProcedureInDataSourcesDB(procedureName, connection, parameters);
            }
        }

        /// <summary>
        /// 执行存储过程（使用连接对象的版本 - 连接池优化）
        /// </summary>
        private async Task<List<Dictionary<string, object>>> ExecuteDynamicProcedureInDataSourcesDB(
            string procedureName, SqlConnection connection, Dictionary<string, object> parameters = null)
        {
            var results = new List<Dictionary<string, object>>();

            using (var command = new SqlCommand(EscapeSqlIdentifier(procedureName), connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null && parameters.Any())
                {
                    foreach (var param in parameters)
                    {
                        object paramValue = param.Value ?? DBNull.Value;
                        command.Parameters.AddWithValue($"@{param.Key}", paramValue);
                        _logger.LogDebug($"为存储过程 {procedureName} 添加参数：@{param.Key} = {paramValue}");
                    }
                }

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var row = new Dictionary<string, object>();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            object value = reader.IsDBNull(i) ? null : reader.GetValue(i);
                            row[reader.GetName(i)] = value;
                        }
                        results.Add(row);
                    }
                }
            }

            _logger.LogInformation($"存储过程 {procedureName} 执行完成，返回 {results.Count} 条数据");
            return results;
        }

        /// <summary>
        /// 执行原始 SQL 查询
        /// </summary>
        private async Task<List<Dictionary<string, object>>> ExecuteRawQueryAsync(
            string sqlQuery, string connectionString, Dictionary<string, object> parameters = null)
        {
            var results = new List<Dictionary<string, object>>();

            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SqlCommand(sqlQuery, connection))
                {
                    if (parameters != null && parameters.Any())
                    {
                        foreach (var param in parameters)
                        {
                            object paramValue = param.Value ?? DBNull.Value;
                            command.Parameters.AddWithValue($"@{param.Key}", paramValue);
                        }
                    }

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var row = new Dictionary<string, object>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                object value = reader.IsDBNull(i) ? null : reader.GetValue(i);
                                row[reader.GetName(i)] = value;
                            }
                            results.Add(row);
                        }
                    }
                }
            }

            return results;
        }

        /// <summary>
        /// SQL 标识符转义（处理存储过程名称包含特殊字符的场景）
        /// </summary>
        private string EscapeSqlIdentifier(string identifier)
        {
            return $"[{identifier.Replace("]", "]]")}]";
        }

        /// <summary>
        /// 屏蔽连接字符串中的敏感信息（如密码），避免日志泄露
        /// </summary>
        private string MaskConnectionString(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                return string.Empty;

            try
            {
                var builder = new SqlConnectionStringBuilder(connectionString);
                if (!string.IsNullOrEmpty(builder.Password))
                    builder.Password = "******";

                return builder.ToString();
            }
            catch
            {
                // 如果解析失败，返回原始字符串但隐藏密码
                var hidden = Regex.Replace(connectionString, @"Password=([^;]+)", "Password=******");
                return Regex.Replace(hidden, @"Pwd=([^;]+)", "Pwd=******");
            }
        }
        #endregion
    }
}